﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using log4net;
using System.Data;
using net.windward.utils.ado;
using System.Configuration;
using System.Data.Common;
using Kailua.net.windward.utils;
using Microsoft.Win32;
using net.windward.api.csharp;
using System.Globalization;

namespace ArrowScheduleCore
{
    /// <summary>
    /// Provides all database access for the Schedule table.
    /// </summary>
    public class ScheduleManager
    {

        private static int ordScheduleId = -1;
        private static int ordTitle;
        private static int ordDescription;
        private static int ordTemplateFilename;
        private static int ordReportFormat;
        private static int ordRunTime;
        private static int ordRepeatType;
        private static int ordRunCycle;
        private static int ordRunPeriod;
        private static int ordRunDay;
        private static int ordRunWeek;
        private static int ordRunWeekday;
        private static int ordSaveFolder;
        private static int ordSaveNum;
        private static int ordEmailTo;
        private static int ordRevStamp;
        private static int ordRunLast;
        private static int ordRunResult;
        private static int ordRunNext;
        private static int ordTimezone;
        private static int ordRunCulture;

        // Arrow 3 fields
        private static int ordSiteUrl;
        private static int ordSystem;
        private static int ordDataProfileGuid;
        private static int ordIsRptAsm;
        private static int ordSaveSiteUrl;

        private static int ordVarScheduleId = -1;
        private static int ordVarName;
        private static int ordVarText;
        private static int ordVarDate;
        private static int ordVarLong;
        private static int ordVarDouble;
        private static int ordVarCalOffset;

        private static readonly ILog log = LogManager.GetLogger(typeof(ScheduleManager));

        private static void GetVarOrdinals(IDataRecord reader)
        {
            if (ordVarScheduleId != -1)
                return;

            ordVarName = reader.GetOrdinal("varName");
            ordVarText = reader.GetOrdinal("varText");
            ordVarDate = reader.GetOrdinal("varDate");
            ordVarLong = reader.GetOrdinal("varLong");
            ordVarDouble = reader.GetOrdinal("varDouble");
            ordVarCalOffset = reader.GetOrdinal("calOffset");
            ordVarScheduleId = reader.GetOrdinal("scheduleId");
        }

        private static void GetOrdinals(IDataRecord reader)
        {
            if (ordScheduleId != -1)
                return;

            ordTitle = reader.GetOrdinal("sTitle");
            ordDescription = reader.GetOrdinal("sDescription");
            ordTemplateFilename = reader.GetOrdinal("templateFilename");
            ordReportFormat = reader.GetOrdinal("reportFormat");
            ordRunTime = reader.GetOrdinal("runTime");
            ordRepeatType = reader.GetOrdinal("repeatType");
            ordRunCycle = reader.GetOrdinal("runCycle");
            ordRunPeriod = reader.GetOrdinal("runPeriod");
            ordRunDay = reader.GetOrdinal("runDay");
            ordRunWeek = reader.GetOrdinal("runWeek");
            ordRunWeekday = reader.GetOrdinal("runWeekday");
            ordSaveFolder = reader.GetOrdinal("saveFolder");
            ordSaveNum = reader.GetOrdinal("saveNum");
            ordEmailTo = reader.GetOrdinal("emailTo");
            ordRevStamp = reader.GetOrdinal("revStamp");
            ordRunLast = reader.GetOrdinal("runLast");
            ordRunResult = reader.GetOrdinal("runResult");
            ordRunNext = reader.GetOrdinal("runNext");
            ordTimezone = reader.GetOrdinal("timezone");
            ordRunCulture = reader.GetOrdinal("runCulture");
            ordScheduleId = reader.GetOrdinal("scheduleId");
            
            // Arrow 3+ specific
            ordSiteUrl = reader.GetOrdinal("tSiteUrl");
            ordSystem = reader.GetOrdinal("system");
            ordDataProfileGuid = reader.GetOrdinal("dspGuid");
            ordIsRptAsm = reader.GetOrdinal("isRptAsm");
            ordSaveSiteUrl = reader.GetOrdinal("saveSiteUrl");
        }

        /// <summary>
        /// Creates a connection to the arrow repository.
        /// </summary>
        /// <returns>The requested connection.</returns>
        public static WrConnection CreateConnection()
        {
            //bugbug: simplify? in WindwardArrow this had some logic, but in ArrowScheduleCore,
            // it's just a call wrapped in another call.
            return new WrConnection("Arrow");
        }

        /// <summary>
        /// Determines if can connect to the arrow repository.
        /// </summary>
        /// <returns>null if can connect, an error message otherwise.</returns>
        public static string CanCreateConnection(bool useDefaults)
        {
            try
            {
                // try config file first
                ConnectionStringSettings connStr = ConfigurationManager.ConnectionStrings["Arrow"];
                if (connStr == null)
                    return GlobalStrings.ScheduleManager_missingArrowConnectionStr;

                DbProviderFactory provider = DbProviderFactories.GetFactory(connStr.ProviderName);

                using (DbConnection conn = provider.CreateConnection())
                {
                    conn.ConnectionString = connStr.ConnectionString;
                    conn.Open();
                    if (conn.State != ConnectionState.Open)
                    {
                        Trap.trap();
                        return String.Format(GlobalStrings.ScheduleManager_schdMgrCantFind, connStr.ConnectionString);
                    }
                    conn.Close();
                }

                return null;
            }
            catch (Exception ex)
            {
                return String.Format(GlobalStrings.genError, ex.Message);
            }
        }

        /// <summary>
        /// Query the database to return a ScheduleItem by its PK.
        /// </summary>
        /// <param name="id">The PK to look for.</param>
        /// <returns>The requested ScheduleItem.</returns>
        public ScheduleItem FindById(long id)
        {

            using (IDbConnection conn = CreateConnection())
            {
                conn.Open();
                using (IDbTransaction txn = conn.BeginTransaction(IsolationLevel.ReadCommitted))
                {
                    using (WrCommand cmd = (WrCommand)conn.CreateCommand())
                    {
                        try
                        {
                            ScheduleItem item;
                            cmd.Transaction = txn;
                            cmd.CommandText = "select * from Schedule where scheduleId = ?";
                            cmd.Set(0, id);

                            using (WrDataReader reader = cmd.WrExecuteReader())
                            {
                                if (!reader.Read())
                                {
                                    if (log.IsDebugEnabled)
                                        log.Debug("FindById(" + id + ") = null");
                                    item = null;
                                }
                                else
                                {
                                    item = BuildScheduleItemFromResultSet(reader);
                                    reader.Close();
                                    cmd.Dispose();
                                    ReadMapVars(conn, txn, item);
                                }
                            }

                            txn.Commit();
                            if (log.IsDebugEnabled)
                                log.Debug("FindById(" + id + ") successful");
                            return item;
                        }
                        catch (Exception ex)
                        {
                            txn.Rollback();
                            if (log.IsWarnEnabled)
                                log.Warn("FindById(" + id + ") failed", ex);
                            throw;
                        }
                    }
                }
            }
        }

        /// <summary>
        /// Get all scheduled items.
        /// </summary>
        /// <returns>The scheduled items we need to run.</returns>
        public ScheduleItemCollection FindAll()
        {

            using (IDbConnection conn = CreateConnection())
            {
                conn.Open();
                using (IDbTransaction txn = conn.BeginTransaction(IsolationLevel.ReadCommitted))
                {
                    using (WrCommand cmd = (WrCommand)conn.CreateCommand())
                    {
                        try
                        {
                            ScheduleItemCollection coll = new ScheduleItemCollection();
                            cmd.Transaction = txn;
                            cmd.CommandText = "select * from Schedule order by scheduleId";

                            using (WrDataReader reader = cmd.WrExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    ScheduleItem item = BuildScheduleItemFromResultSet(reader);
                                    if (item != null)
                                        coll.Add(item);
                                }
                            }
                            cmd.Dispose();

                            // get the collections
                            foreach (ScheduleItem item in coll)
                                ReadMapVars(conn, txn, item);

                            txn.Commit();
                            if (log.IsDebugEnabled)
                                log.Debug("FindAll() Count = " + coll.Count);
                            return coll;
                        }
                        catch (Exception ex)
                        {
                            txn.Rollback();
                            log.Error("FindAll() failed", ex);
                            throw;
                        }
                    }
                }
            }
        }
        /// <summary>
        /// Find the next item we need to run. This can be before or after now.
        /// </summary>
        /// <returns>The next item to run. null if no items</returns>
        public ScheduleItem FindNext()
        {

            using (IDbConnection conn = CreateConnection())
            {
                conn.Open();
                using (IDbTransaction txn = conn.BeginTransaction(IsolationLevel.ReadCommitted))
                {
                    using (WrCommand cmd = (WrCommand)conn.CreateCommand())
                    {
                        try
                        {
                            ScheduleItem item;
                            cmd.Transaction = txn;
                            cmd.CommandText = "select * from Schedule order by runNext, scheduleId top 1";

                            using (WrDataReader reader = cmd.WrExecuteReader())
                            {
                                if (!reader.Read())
                                {
                                    if (log.IsDebugEnabled)
                                        log.Debug("FindNext() = null");
                                    item = null;
                                }
                                else
                                {
                                    item = BuildScheduleItemFromResultSet(reader);
                                    reader.Close();
                                    cmd.Dispose();

                                    ReadMapVars(conn, txn, item);
                                    if (log.IsDebugEnabled)
                                        log.Debug("FindNext() = " + item.Id);
                                }
                            }

                            txn.Commit();
                            return item;
                        }
                        catch (Exception ex)
                        {
                            txn.Rollback();
                            if (log.IsWarnEnabled)
                                log.Warn("FindNext() failed", ex);
                            throw;
                        }
                    }
                }
            }
        }

        /// <summary>
        /// Get all scheduled items that are past due or due to run.
        /// </summary>
        /// <returns>The scheduled items we need to run.</returns>
        public ScheduleItemCollection FindAllScheduleDue()
        {

            using (IDbConnection conn = CreateConnection())
            {
                conn.Open();
                using (IDbTransaction txn = conn.BeginTransaction(IsolationLevel.ReadCommitted))
                {
                    using (WrCommand cmd = (WrCommand)conn.CreateCommand())
                    {
                        try
                        {
                            ScheduleItemCollection coll = new ScheduleItemCollection();
                            cmd.Transaction = txn;
                            cmd.CommandText = "select * from Schedule where runNext <= ? order by runNext, scheduleId";
                            cmd.Set(0, DateTime.UtcNow);

                            using (WrDataReader reader = cmd.WrExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    ScheduleItem item = BuildScheduleItemFromResultSet(reader);
                                    if (item != null)
                                        coll.Add(item);
                                }
                            }
                            cmd.Dispose();

                            // get the collections
                            foreach (ScheduleItem item in coll)
                                ReadMapVars(conn, txn, item);

                            txn.Commit();
                            if (log.IsDebugEnabled)
                                log.Debug("FindAllLocal() Count = " + coll.Count);
                            return coll;
                        }
                        catch (Exception ex)
                        {
                            txn.Rollback();
                            log.Error("FindAllGlobal() failed", ex);
                            throw;
                        }
                    }
                }
            }
        }

        private static ScheduleItem BuildScheduleItemFromResultSet(WrDataReader reader)
        {

            GetOrdinals(reader);

            SchedulePeriod.REPEAT type = (SchedulePeriod.REPEAT)reader.GetByte(ordRepeatType);
            SchedulePeriod period;
            switch (type)
            {
                case SchedulePeriod.REPEAT.DAILY:
                    period = new ScheduleDaily((ScheduleDaily.CYCLE)reader.GetByte(ordRunCycle), reader.GetByte(ordRunPeriod));
                    break;
                case SchedulePeriod.REPEAT.WEEKLY:
                    period = new ScheduleWeekly(reader.GetByte(ordRunPeriod), (ScheduleWeekly.DAYS)reader.GetByte(ordRunCycle));
                    break;
                case SchedulePeriod.REPEAT.MONTHLY:
                    period = new ScheduleMonthly((ScheduleMonth.CYCLE)reader.GetByte(ordRunCycle), reader.GetByte(ordRunDay), (ScheduleMonth.WEEK)reader.GetByte(ordRunWeek), (ScheduleMonth.WEEKDAY)reader.GetByte(ordRunWeekday), reader.GetByte(ordRunPeriod));
                    break;
                case SchedulePeriod.REPEAT.YEARLY:
                    period = new ScheduleYearly((ScheduleMonth.CYCLE)reader.GetByte(ordRunCycle), reader.GetByte(ordRunDay), (ScheduleYearly.MONTH)reader.GetByte(ordRunPeriod), (ScheduleMonth.WEEK)reader.GetByte(ordRunWeek), (ScheduleMonth.WEEKDAY)reader.GetByte(ordRunWeekday));
                    break;
                default:
                    throw new ArgumentOutOfRangeException("unknown type: " + type);
            }

            // RunTime is in local (timezone defined) time. This may not be local time
            //period.RunZone = WindwardTimeZone.GetTimeZone(reader.GetString(ordTimezone));
            period.RunZone = TimeZoneInfo.FindSystemTimeZoneById(reader.GetString(ordTimezone).Trim());
            period.RunTime = new DateTime(reader.GetDateTime(ordRunTime).Ticks, DateTimeKind.Local); // may not be local time - it's RunZone time

            ScheduleItem item = new ScheduleItem(reader.GetInt64(ordScheduleId),
                    reader.GetString(ordTitle),
                    reader[ordDescription] != DBNull.Value ? reader.GetString(ordDescription) : null,
                    reader.GetString(ordTemplateFilename),
                    (Report.REPORT_TYPE)reader.GetByte(ordReportFormat),
                    period,
                    reader[ordSaveFolder] != DBNull.Value ? reader.GetString(ordSaveFolder) : null,
                    reader[ordSaveNum] != DBNull.Value ? reader.GetInt32(ordSaveNum) : 0,
                    reader[ordEmailTo] != DBNull.Value ? reader.GetString(ordEmailTo) : null,
                    reader.GetTimestamp(ordRevStamp),
                    reader[ordRunLast] != DBNull.Value ? new DateTime(reader.GetDateTime(ordRunLast).Ticks, DateTimeKind.Utc) : DateTime.MinValue,	// UTC
                    reader[ordRunResult] != DBNull.Value ? reader.GetString(ordRunResult) : null,
                    new DateTime(reader.GetDateTime(ordRunNext).Ticks, DateTimeKind.Utc));	// UTC
            string locale = reader.GetString(ordRunCulture).Trim();
            period.Culture = locale.Length > 0 ? new CultureInfo(locale) : CultureInfo.CurrentUICulture;

            // Arrow 3+ fields for sharepoint
            item.SiteUrl = reader.GetString(ordSiteUrl);
            item.System = reader.GetString(ordSystem);
            item.DataProfileGuid = reader.GetString(ordDataProfileGuid);
            item.IsRptAsm = Boolean.Parse(reader.GetString(ordIsRptAsm));
            item.SaveSiteUrl = reader.GetString(ordSaveSiteUrl);

            Trap.trap(item.Period.RunTime.Kind != DateTimeKind.Local);
            Trap.trap((item.RunLast.Date != DateTime.MinValue.Date) && (item.RunLast.Kind != DateTimeKind.Utc));
            Trap.trap((item.RunNext.Kind != DateTimeKind.Utc) && (item.RunNext.Date != DateTime.MaxValue.Date));
            return item;
        }

        private static void ReadMapVars(IDbConnection conn, IDbTransaction txn, ScheduleItem item)
        {

            //maps
            using (WrCommand cmd = (WrCommand)conn.CreateCommand())
            {
                cmd.Transaction = txn;
                cmd.CommandText = "select * from ScheduleVars where scheduleId = ? order by varName";
                cmd.Set(0, item.Id);
                using (IDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        GetVarOrdinals(reader);

                        // find which one is good
                        object varValue = null;
                        if (!reader.IsDBNull(ordVarText))
                            varValue = reader.GetString(ordVarText);
                        else if (!reader.IsDBNull(ordVarLong))
                            varValue = reader.GetInt64(ordVarLong);
                        else if (!reader.IsDBNull(ordVarDate))
                            varValue = reader.GetDateTime(ordVarDate);
                        else if (!reader.IsDBNull(ordVarDouble))
                            varValue = reader.GetDouble(ordVarDouble);

                        ScheduleVarItem tmi = new ScheduleVarItem(reader.GetInt64(ordVarScheduleId), reader.GetString(ordVarName),
                                                                  varValue, (ScheduleVarItem.CAL_OFFSET)reader.GetByte(ordVarCalOffset));
                        if ((varValue == null) && (tmi.CalOffset == ScheduleVarItem.CAL_OFFSET.FIXED))
                            throw new ApplicationException(String.Format("Variable in schedule {0} has a null value, not a calendar offset", item.Id));
                        item.Variables.Add(tmi);
                    }
                }
            }
        }

        /// <summary>
        /// Create a new row in the Schedule table.
        /// </summary>
        /// <param name="item">The values used to populate this row in the table.</param>
        public void Insert(ScheduleItem item)
        {

            Trap.trap(item.Period.RunTime.Kind != DateTimeKind.Local);
            Trap.trap((item.RunLast.Date != DateTime.MinValue.Date) && (item.RunLast.Kind != DateTimeKind.Utc));
            Trap.trap((item.RunNext.Kind != DateTimeKind.Utc) && (item.RunNext.Date != DateTime.MaxValue.Date));
            if (log.IsDebugEnabled)
                log.Debug("Insert: " + item);

            using (IDbConnection conn = CreateConnection())
            {
                conn.Open();
                using (IDbTransaction txn = conn.BeginTransaction(IsolationLevel.ReadCommitted))
                {
                    try
                    {
                        // template
                        long id;
                        using (WrCommand cmd = (WrCommand)conn.CreateCommand())
                        {
                            // bugbug: tomas arrow 3
                            string arrow3Columns = "";
                            string arrow3Values = "";
                            // Arrow 3+ specific
                            arrow3Columns = ", tSiteUrl, system, dspGuid, isRptAsm, saveSiteUrl";
                            arrow3Values = ", ?, ?, ?, ?, ?"; // 19, 20, 21, 22, 23

                            cmd.Transaction = txn;
                            cmd.CommandText = "insert into Schedule (sTitle, sDescription, templateFileName, reportFormat, runTime, " +
                                        "repeatType, runCycle, runPeriod, runDay, runWeek, runWeekday, saveFolder, saveNum, emailTo, " +
                                        "runLast, runResult, runNext, timezone, runCulture" + arrow3Columns + ") " +
                                        "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?" + arrow3Values +
                                        ")";
                            cmd.Set(0, item.Title);
                            cmd.Set(1, item.Description);
                            cmd.Set(2, item.TemplateFileName);
                            cmd.Set(3, (byte)item.Format);
                            Trap.trap(item.Period.RunTime.Kind != DateTimeKind.Local);
                            cmd.Set(4, item.Period.RunTime); // local time
                            cmd.Set(5, (byte)item.Period.RepeatType);

                            switch (item.Period.RepeatType)
                            {
                                case SchedulePeriod.REPEAT.DAILY:
                                    ScheduleDaily daily = (ScheduleDaily)item.Period;
                                    cmd.Set(6, (byte)daily.Cycle);
                                    cmd.Set(7, (byte)daily.Period);
                                    cmd.Set(8, null);
                                    cmd.Set(9, null);
                                    cmd.Set(10, null);
                                    break;
                                case SchedulePeriod.REPEAT.WEEKLY:
                                    ScheduleWeekly weekly = (ScheduleWeekly)item.Period;
                                    cmd.Set(6, (byte)weekly.Days);
                                    cmd.Set(7, (byte)weekly.Period);
                                    cmd.Set(8, null);
                                    cmd.Set(9, null);
                                    cmd.Set(10, null);
                                    break;
                                case SchedulePeriod.REPEAT.MONTHLY:
                                    ScheduleMonthly monthly = (ScheduleMonthly)item.Period;
                                    cmd.Set(6, (byte)monthly.Cycle);
                                    cmd.Set(7, (byte)monthly.Period);
                                    cmd.Set(8, (byte)monthly.Day);
                                    cmd.Set(9, (byte)monthly.Week);
                                    cmd.Set(10, (byte)monthly.Weekday);
                                    break;
                                case SchedulePeriod.REPEAT.YEARLY:
                                    ScheduleYearly yearly = (ScheduleYearly)item.Period;
                                    cmd.Set(6, (byte)yearly.Cycle);
                                    cmd.Set(7, (byte)yearly.Month);
                                    cmd.Set(8, (byte)yearly.Day);
                                    cmd.Set(9, (byte)yearly.Week);
                                    cmd.Set(10, (byte)yearly.Weekday);
                                    break;
                            }
                            cmd.Set(11, item.SaveFolder);
                            if (item.SaveNum > 0)
                                cmd.Set(12, item.SaveNum);
                            else
                                cmd.Set(12, null);
                            cmd.Set(13, item.EmailTo);
                            if (item.RunLast == DateTime.MinValue)
                                cmd.Set(14, null);
                            else
                            {
                                Trap.trap(item.RunLast.Kind != DateTimeKind.Utc);
                                cmd.Set(14, item.RunLast); // UTC
                            }
                            cmd.Set(15, item.RunResult);
                            Trap.trap(item.RunNext.Kind != DateTimeKind.Utc);
                            cmd.Set(16, item.RunNext); // UTC
                            //cmd.Set(17, item.Period.RunZone.Key); // timezone for runTime
                            cmd.Set(17, item.Period.RunZone.Id); // timezone for runTime
                            cmd.Set(18, item.Period.Culture.IetfLanguageTag);

                            // bugbug: tomas arrow 3
                            // Arrow 3+ specific
                            cmd.Set(19, item.SiteUrl); // template/rpt def site url
                            cmd.Set(20, item.System); // system
                            cmd.Set(21, item.DataProfileGuid); // dspGuid
                            cmd.Set(22, item.IsRptAsm.ToString()); // is report assembly
                            cmd.Set(23, item.SaveSiteUrl);

                            id = cmd.ExecuteInsert("scheduleId");
                            item.setId(id);
                        }

                        // maps
                        InsertVarItems(item, conn, txn, id);

                        txn.Commit();
                        if (log.IsDebugEnabled)
                            log.Debug("Insert(" + item.Title + ") = " + id);
                    }
                    catch (Exception ex)
                    {
                        txn.Rollback();
                        log.Error("Insert(" + item.Title + ") failed", ex);
                        throw;
                    }
                }
            }
        }

        /// <summary>
        /// Update this row in the database.
        /// </summary>
        /// <param name="item">The new values to populate this row.</param>
        /// <exception cref="DeletedDataException">Thrown if this row no longer exists in the database.</exception>
        /// <exception cref="ExpiredDataException">Thrown if another user updated this row under this item.</exception>
        /// <returns>The number of rows updated. Should always be 1.</returns>
        public int Update(ScheduleItem item)
        {

            if (log.IsDebugEnabled)
                log.Debug("Update: " + item);
            Trap.trap(item.Period.RunTime.Kind != DateTimeKind.Local);
            Trap.trap((item.RunLast.Date != DateTime.MinValue.Date) && (item.RunLast.Kind != DateTimeKind.Utc));
            Trap.trap((item.RunNext.Kind != DateTimeKind.Utc) && (item.RunNext.Date != DateTime.MaxValue.Date));

            using (IDbConnection conn = CreateConnection())
            {
                conn.Open();
                using (IDbTransaction txn = conn.BeginTransaction(IsolationLevel.ReadCommitted))
                {
                    try
                    {
                        // first we check the timestamp against item. If they don't match we throw an exception.
                        using (WrCommand cmd = (WrCommand)conn.CreateCommand())
                        {
                            cmd.Transaction = txn;
                            cmd.CommandText = "select revStamp from Schedule where scheduleId = ?";
                            cmd.Set(0, item.Id);

                            using (WrDataReader reader = cmd.WrExecuteReader())
                            {
                                if (!reader.Read())
                                    throw new DeletedDataException("Schedule " + item.Id + " is deleted. Cannot be updated");
                                long timestamp = reader.GetTimestamp(0);
                                if (timestamp != item.Timestamp)
                                    throw new ExpiredDataException("Schedule " + item.Id + " was updated after read. Cannot be updated");
                            }
                        }

                        // ok, we can update - let's do it
                        // template
                        int num;
                        using (WrCommand cmd = (WrCommand)conn.CreateCommand())
                        {
                            cmd.Transaction = txn;
                            cmd.CommandText = "update Schedule set sTitle=?, sDescription=?, templateFileName=?, reportFormat=?, runTime=?, " +
                                              "repeatType=?, runCycle=?, runPeriod=?, runDay=?, runWeek=?, runWeekday=?, saveFolder=?, " +
                                              "saveNum=?, emailTo=?, runLast=?, runResult=?, runNext=?, timezone=?, runCulture=? " +
                                "where scheduleId=?";
                            cmd.Set(0, item.Title);
                            cmd.Set(1, item.Description);
                            cmd.Set(2, item.TemplateFileName);
                            cmd.Set(3, (Byte)item.Format);
                            Trap.trap(item.Period.RunTime.Kind != DateTimeKind.Local);
                            cmd.Set(4, item.Period.RunTime); // local time
                            cmd.Set(5, (Byte)item.Period.RepeatType);

                            switch (item.Period.RepeatType)
                            {
                                case SchedulePeriod.REPEAT.DAILY:
                                    ScheduleDaily daily = (ScheduleDaily)item.Period;
                                    cmd.Set(6, (Byte)daily.Cycle);
                                    cmd.Set(7, (Byte)daily.Period);
                                    cmd.Set(8, null);
                                    cmd.Set(9, null);
                                    cmd.Set(10, null);
                                    break;
                                case SchedulePeriod.REPEAT.WEEKLY:
                                    ScheduleWeekly weekly = (ScheduleWeekly)item.Period;
                                    cmd.Set(6, (Byte)weekly.Days);
                                    cmd.Set(7, (Byte)weekly.Period);
                                    cmd.Set(8, null);
                                    cmd.Set(9, null);
                                    cmd.Set(10, null);
                                    break;
                                case SchedulePeriod.REPEAT.MONTHLY:
                                    ScheduleMonthly monthly = (ScheduleMonthly)item.Period;
                                    cmd.Set(6, (Byte)monthly.Cycle);
                                    cmd.Set(7, (Byte)monthly.Period);
                                    cmd.Set(8, (Byte)monthly.Day);
                                    cmd.Set(9, (Byte)monthly.Week);
                                    cmd.Set(10, (Byte)monthly.Weekday);
                                    break;
                                case SchedulePeriod.REPEAT.YEARLY:
                                    ScheduleYearly yearly = (ScheduleYearly)item.Period;
                                    cmd.Set(6, (Byte)yearly.Cycle);
                                    cmd.Set(7, (Byte)yearly.Month);
                                    cmd.Set(8, (Byte)yearly.Day);
                                    cmd.Set(9, (Byte)yearly.Week);
                                    cmd.Set(10, (Byte)yearly.Weekday);
                                    break;
                            }
                            cmd.Set(11, item.SaveFolder);
                            if (item.SaveNum > 0)
                                cmd.Set(12, item.SaveNum);
                            else
                                cmd.Set(12, null);
                            cmd.Set(13, item.EmailTo);
                            if (item.RunLast == DateTime.MinValue)
                                cmd.Set(14, null);
                            else
                            {
                                Trap.trap(item.RunLast.Kind != DateTimeKind.Utc);
                                cmd.Set(14, item.RunLast); // UTC
                            }
                            cmd.Set(15, item.RunResult);
                            Trap.trap(item.RunNext.Kind != DateTimeKind.Utc);
                            cmd.Set(16, item.RunNext); // UTC
                            //cmd.Set(17, item.Period.RunZone.Key);
                            cmd.Set(17, item.Period.RunZone.Id);
                            cmd.Set(18, item.Period.Culture.IetfLanguageTag);

                            cmd.Set(19, item.Id);
                            num = cmd.ExecuteNonQuery();
                        }

                        // maps - replace all
                        using (WrCommand cmd = (WrCommand)conn.CreateCommand())
                        {
                            cmd.Transaction = txn;
                            cmd.CommandText = "delete from ScheduleVars where scheduleId = ?";
                            cmd.Set(0, item.Id);
                            cmd.ExecuteNonQuery();
                        }
                        InsertVarItems(item, conn, txn, item.Id);

                        txn.Commit();
                        if (log.IsDebugEnabled)
                            log.Debug("Update(" + item.Id + ", " + item.Title + ") successful");
                        return num;
                    }
                    catch (Exception ex)
                    {
                        txn.Rollback();
                        log.Error(String.Format("Update({0}) failed", item.Id), ex);
                        throw;
                    }
                }
            }
        }

        private static void InsertVarItems(ScheduleItem item, IDbConnection conn, IDbTransaction txn, long id)
        {

            foreach (ScheduleVarItem svi in item.Variables)
                using (WrCommand cmd = (WrCommand)conn.CreateCommand())
                {
                    cmd.Transaction = txn;
                    // date is CAL_OFFSET != 0 or value is a DateTime - rest are just the object type of value.
                    if ((svi.Value is DateTime) || ((svi.CalOffset != ScheduleVarItem.CAL_OFFSET.FIXED) && (svi.Value == null)))
                    {
                        cmd.CommandText = "insert into ScheduleVars (scheduleId, varName, varDate, calOffset) VALUES (?, ?, ?, ?)";
                        cmd.Set(0, id);
                        cmd.Set(1, svi.Name);
                        cmd.Set(2, svi.CalOffset == ScheduleVarItem.CAL_OFFSET.FIXED ? svi.Value : null);
                        cmd.Set(3, (Byte)svi.CalOffset);
                    }
                    else if (svi.Value is string)
                    {
                        cmd.CommandText = "insert into ScheduleVars (scheduleId, varName, varText, calOffset) VALUES (?, ?, ?, ?)";
                        cmd.Set(0, id);
                        cmd.Set(1, svi.Name);
                        cmd.Set(2, svi.Value);
                        cmd.Set(3, (Byte)ScheduleVarItem.CAL_OFFSET.FIXED);
                    }
                    else if ((svi.Value is float) || (svi.Value is double) || (svi.Value is decimal))
                    {
                        cmd.CommandText = "insert into ScheduleVars (scheduleId, varName, varDouble, calOffset) VALUES (?, ?, ?, ?)";
                        cmd.Set(0, id);
                        cmd.Set(1, svi.Name);
                        cmd.Set(2, svi.Value);
                        cmd.Set(3, (Byte)ScheduleVarItem.CAL_OFFSET.FIXED);
                    }
                    else if ((svi.Value is bool) || (svi.Value is byte) || (svi.Value is sbyte) || (svi.Value is char) || (svi.Value is int)
                         || (svi.Value is uint) || (svi.Value is long) || (svi.Value is ulong) || (svi.Value is short) || (svi.Value is ushort))
                    {
                        Trap.trap(svi.Value is bool);
                        Trap.trap(svi.Value is byte);
                        Trap.trap(svi.Value is sbyte);
                        Trap.trap(svi.Value is char);
                        Trap.trap(svi.Value is uint);
                        Trap.trap(svi.Value is ulong);
                        Trap.trap(svi.Value is short);
                        Trap.trap(svi.Value is ushort);
                        cmd.CommandText = "insert into ScheduleVars (scheduleId, varName, varLong, calOffset) VALUES (?, ?, ?, ?)";
                        cmd.Set(0, id);
                        cmd.Set(1, svi.Name);
                        cmd.Set(2, svi.Value);
                        cmd.Set(3, (Byte)ScheduleVarItem.CAL_OFFSET.FIXED);
                    }
                    else
                    {
                        // this should never happen!
                        Trap.trap();
                        cmd.CommandText = "insert into ScheduleVars (scheduleId, varName, varText, calOffset) VALUES (?, ?, ?, ?)";
                        cmd.Set(0, id);
                        cmd.Set(1, svi.Name);
                        cmd.Set(2, svi.Value == null ? "" : svi.Value.ToString());
                        cmd.Set(3, (Byte)ScheduleVarItem.CAL_OFFSET.FIXED);
                    }

                    cmd.ExecuteNonQuery();
                    svi.ScheduleId = id;
                }
        }

        /// <summary>
        /// Delete this ScheduleItem from the table.
        /// </summary>
        /// <param name="id">The primary key to delete.</param>
        /// <returns>The number of rows deleted. Should always be 1 (deleted) or 0(doesn't exist).</returns>
        public int Delete(long id)
        {

            if (log.IsDebugEnabled)
                log.Debug("Delete: " + id);

            using (IDbConnection conn = CreateConnection())
            {
                conn.Open();
                using (IDbTransaction txn = conn.BeginTransaction(IsolationLevel.ReadCommitted))
                {
                    try
                    {
                        // have to delete run items because they have a FK:PK to this.
                        using (WrCommand cmd = (WrCommand)conn.CreateCommand())
                        {
                            cmd.Transaction = txn;
                            cmd.CommandText = "delete from ScheduleRun where scheduleId = ?";
                            cmd.Set(0, id);
                            cmd.ExecuteNonQuery();
                        }

                        using (WrCommand cmd = (WrCommand)conn.CreateCommand())
                        {
                            cmd.Transaction = txn;
                            cmd.CommandText = "delete from ScheduleVars where scheduleId = ?";
                            cmd.Set(0, id);
                            cmd.ExecuteNonQuery();
                        }

                        using (WrCommand cmd = (WrCommand)conn.CreateCommand())
                        {
                            cmd.Transaction = txn;
                            cmd.CommandText = "delete from Schedule where scheduleId = ?";
                            cmd.Set(0, id);
                            int num = cmd.ExecuteNonQuery();

                            txn.Commit();
                            if (log.IsDebugEnabled)
                                log.Debug("Delete(" + id + ") = " + num);
                            return num;
                        }
                    }
                    catch (Exception ex)
                    {
                        txn.Rollback();
                        log.Error(String.Format("Delete({0}) failed", id), ex);
                        throw;
                    }
                }
            }
        }
    }
}
